Imports System.Data.SqlClient
Imports System.Drawing
Imports DevExpress.XtraGrid
Imports DevExpress.XtraGrid.Views.Base
Imports DevExpress.XtraGrid.Columns
Imports DevExpress.XtraEditors
Imports DevExpress.XtraScheduler
Imports DevExpress.XtraScheduler.UI
Imports DevExpress.XtraEditors.Repository

'--- Created Date On : 11-Oct-2010 (Jagdish)

Public Class frmClearCheck
    Dim SaveData As Boolean
    Const CheckPaymentId As Integer = 4 'this is PaymentTypeId for CheckPayment in LtblPaymentType
    Dim rpgcmbStatus As RepositoryItemGridLookUpEdit
    Dim mblnChanged As Boolean
    Public Enum CheckStatus
        Pending = 1
        Cleared = 2
        Returned = 3
    End Enum
    Private Sub frmClearCheck_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Call SetImageControl(Me)
        Call GetAccessPermission(Me, "frmClearCheck")
        rgbCheckStatus.SelectedIndex = 0
        Call PopulateStatusGrid(1)
    End Sub

    Private Sub PopulateStatusGrid(ByVal StatusId As Integer)
        'code to populate grid with check status statusid
        Call SetStatusEditorDataColumn()
        Dim strQuery As String = "SELECT CheckId, Number, Status  FROM   tblCheck WHERE Status=" & StatusId & " ORDER BY Number"
        Dim dsTemp As DataSet = FillDataSet(strQuery)
        With grdRecords
            .BeginUpdate()
            .DataSource = dsTemp.Tables(0)
            .EndUpdate()
        End With
        'store status corresponding to this button in datagrid. This is useful while saving
        grdRecords.Tag = StatusId
    End Sub

    Private Sub InitialiseStatusDataEntry()
        Dim dtTemp As DataTable
        dtTemp = New DataTable("Status")
        With dtTemp.Columns
            .Add("CheckId", GetType(Integer))
            .Add("Number", GetType(String))
            .Add("Status", GetType(Integer))
        End With
        grdRecords.DataSource = dtTemp
        gdvRecords.Columns("CheckId").Visible = False
        dtTemp.Dispose()
    End Sub

    Private Sub SetStatusEditorDataColumn()
        Try
            Call InitialiseStatusDataEntry()
            Dim fnBold As New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold)
            With gdvRecords
                .Columns("CheckId").Visible = False
                .Columns("CheckId").OptionsColumn.ShowInCustomizationForm = False
                .Columns("Number").AppearanceHeader.Font = fnBold
                .Columns("Number").Width = 100
                .Columns("Number").OptionsColumn.AllowEdit = False
                .Columns("Status").AppearanceHeader.Font = fnBold
            End With

            'dtStatus is a datatable which is used to bind to combobox
            Dim dtStatus As New DataTable
            dtStatus.Columns.Add(New DataColumn("Status", GetType(String)))
            dtStatus.Columns.Add(New DataColumn("StatusId", GetType(Integer)))

            'add rows to datatable
            Dim row As DataRow = dtStatus.NewRow
            row.Item("Status") = "Pending" : row.Item("StatusId") = CheckStatus.Pending : dtStatus.Rows.Add(row)

            row = dtStatus.NewRow
            row.Item("Status") = "Cleared" : row.Item("StatusId") = CheckStatus.Cleared : dtStatus.Rows.Add(row)

            row = dtStatus.NewRow
            row.Item("Status") = "Returned" : row.Item("StatusId") = CheckStatus.Returned : dtStatus.Rows.Add(row)

            'create combo box control to put it on grid
            rpgcmbStatus = New RepositoryItemGridLookUpEdit

            With rpgcmbStatus
                .TextEditStyle = ComboBoxStyle.DropDownList
                .DisplayMember = "Status"
                .ValueMember = "StatusId"
                .DataSource = dtStatus
                .View.Columns("StatusId").Visible = False
            End With



            grdRecords.RepositoryItems.AddRange(New DevExpress.XtraEditors.Repository.RepositoryItem() {rpgcmbStatus})
            With gdvRecords
                .Columns("Status").ColumnEdit = rpgcmbStatus
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
            Call AddErrorLog("Error " & Err.Number & " (" & Err.Description & ") in Procedure " & _
                 Reflection.MethodBase.GetCurrentMethod.Name & " Of " & Me.Name)
        End Try
    End Sub

    Private Sub rgbCheckStatus_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rgbCheckStatus.SelectedIndexChanged
        Call PopulateStatusGrid(rgbCheckStatus.SelectedIndex + 1)
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim strQuery As String = ""
        Dim blnIsTransactionOpen As Boolean
        Try
            gtrnWebPlusMed = gconWebPlusMed.BeginTransaction
            blnIsTransactionOpen = True

            Dim dt As DataTable = grdRecords.DataSource
            For Each r As DataRow In dt.Rows
                If r.Item("Status") <> grdRecords.Tag Then
                    'With dcUpdate
                    '    .Parameters("@Status").Value = r.Item("Status")
                    '    .Parameters("@CheckId").Value = r.Item("CheckId")
                    '    .ExecuteNonQuery()
                    'End With
                    Call UpdateTable("tblCheck", "Status=" & r.Item("Status"), "CheckId=" & r.Item("CheckId"))
                End If
            Next

            'Call PopulateStatusGrid(rgbCheckStatus.SelectedIndex + 1)
            PopulateStatusGrid(grdRecords.Tag)
            'if grid shows only Cleared checks and user has changed it to returned then call procedure.
            'No action for checks which are changed from cleared to pending
            If grdRecords.Tag = CheckStatus.Cleared Then
                UpdatePaymentTable(dt.Select("Status=" & CheckStatus.Returned), CheckStatus.Returned)
            ElseIf grdRecords.Tag = CheckStatus.Pending Then
                'same thing for pending checks.Take action if status is changed from pending to Returned
                UpdatePaymentTable(dt.Select("Status=" & CheckStatus.Returned), CheckStatus.Returned)
            ElseIf grdRecords.Tag = CheckStatus.Returned Then
                'For returned checks ,take action if status is changed to pending or clear
                UpdatePaymentTable(dt.Select("Status=" & CheckStatus.Cleared & " OR Status=" & CheckStatus.Pending), CheckStatus.Cleared)
            End If

            gtrnWebPlusMed.Commit()
            blnIsTransactionOpen = False
        Catch ex As Exception
            If blnIsTransactionOpen = True Then
                gtrnWebPlusMed.Rollback()
            End If
            MessageBox.Show(ex.Message, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
            AddErrorLog("Error " & Err.Number & " (" & Err.Description & ") in Procedure " & _
                Reflection.MethodBase.GetCurrentMethod.Name & " Of " & Me.Name)
        End Try
    End Sub

    'RowArray contains those rows which needs to be updated.
    'StatusOfCheck is status for selected rows.
    Private Sub UpdatePaymentTable(ByVal RowArray As DataRow(), ByVal StatusOfCheck As CheckStatus)
        Dim ValidQuery As Boolean
        Dim dt As New DataTable
        'For the following query we are looking for  absolute value of Payment
        'This is necessary because we are interested in Payement and associated data. Using ABS we are getting only one row.
        Dim Ssql As String
        Dim SsqlDelete As String = "DELETE FROM tblPayment WHERE"
        Dim strQuery As String
        Dim dsTemp As DataSet

        Dim PaymentValue As Integer = IIf(StatusOfCheck = CheckStatus.Returned, -1, 1)

        If StatusOfCheck <> CheckStatus.Returned Then
            Ssql = "Payment < 0 AND CheckId IN ("
            For Each row As DataRow In RowArray
                If row("Status") = CheckStatus.Pending OrElse row("Status") = CheckStatus.Cleared Then
                    Ssql = Ssql & row.Item("CheckId") & ","
                    ValidQuery = True
                End If
            Next

        Else
            Ssql = "SELECT DISTINCT PatientId, PaymentTypeId, PracticeId,  ABS(Payment) AS Payment,InsuranceCompanyId,CheckId FROM tblPayment WHERE CheckId IN ("
            For Each row As DataRow In RowArray
                If row("Status") = StatusOfCheck Then
                    Ssql = Ssql & row.Item("CheckId") & ","
                    ValidQuery = True
                End If
            Next
        End If


        If ValidQuery Then
            'Remove last comma from query
            Ssql = Ssql.Substring(0, Ssql.Length - 1)
            'complete query
            Ssql = Ssql & ")"

            Try
                If StatusOfCheck = CheckStatus.Cleared OrElse StatusOfCheck = CheckStatus.Pending Then
                    Call DeleteFromTable("tblPayment", Ssql)
                ElseIf StatusOfCheck = CheckStatus.Returned Then
                    dsTemp = FillDataSet(Ssql)

                    For I As Integer = 0 To dsTemp.Tables(0).Rows.Count - 1
                        strQuery = dsTemp.Tables(0).Rows(I).Item("PatientId")
                        strQuery = strQuery & "," & dsTemp.Tables(0).Rows(I).Item("PaymentTypeId")
                        strQuery = strQuery & "," & dsTemp.Tables(0).Rows(I).Item("PracticeId")
                        strQuery = strQuery & ",'" & Format(CDate(Today), gstrDtFormat)
                        strQuery = strQuery & "'," & dsTemp.Tables(0).Rows(I).Item("Payment") * PaymentValue
                        If IsDBNullOrNothing(dsTemp.Tables(0).Rows(I).Item("InsuranceCompanyId")) Then
                            strQuery = strQuery & ",Null"
                        Else
                            strQuery = strQuery & "," & dsTemp.Tables(0).Rows(I).Item("InsuranceCompanyId")
                        End If
                        strQuery = strQuery & "," & dsTemp.Tables(0).Rows(I).Item("CheckId")
                        Call InsertintoTable("tblPayment", strQuery)

                    Next
                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
                AddErrorLog("Error " & Err.Number & " (" & Err.Description & ") in Procedure " & _
                    Reflection.MethodBase.GetCurrentMethod.Name & " Of " & Me.Name)
            End Try
        End If
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub gdvRecords_CellValueChanged(ByVal sender As Object, ByVal e As DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs) Handles gdvRecords.CellValueChanged
        mblnChanged = False
    End Sub
End Class
